﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.Text.RegularExpressions;
using Lime49;

namespace ResourceBlender.DAL {
    public class ResourceBlenderDAL {
        /// <summary>
        /// Adds a sample application to the database.
        /// </summary>
        public static void AddDummyData() {
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                db.AddDummyData();
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Loads a sorted dictionary containing the languages of all resources in the database, sorted by English Name. 
        /// Only existing resources are used to populate the dictionary, and only non empty language codes are included.
        /// Key = LanguageCode, Value = CultureInfo
        /// </summary>
        /// <returns>A dictionary containing the CultureInfo of all resources in the database</returns>
        public static IEnumerable<KeyValuePair<string, CultureInfo>> GetUniqueLanguages() {
            Database db = null;
            List<KeyValuePair<string, CultureInfo>> languages = new List<KeyValuePair<string, CultureInfo>>();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT DISTINCT language FROM {0}resources", db.TablePrefix);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        string code = (reader[0] ?? string.Empty).ToString().Trim();
                        if(code.Length == 0)
                            continue;
                        CultureInfo info = Utils.GetCultureInfo(code);
                        if(info != null) {
                            languages.Add(new KeyValuePair<string, CultureInfo>(info.Name, info));
                        }
                    }
                    reader.Close();
                }
            } catch(Exception ex) {
                throw ex;
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            var sorted = from lang in languages
                         orderby lang.Value.EnglishName ascending
                         select lang;
            return sorted;
        }

        #region Applications
        /// <summary>
        /// Adds a new application.
        /// </summary>
        /// <param name="application">The application.</param>
        public static void AddApplication(RBApplication application) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmd = db.CreateCommand(trans)) {
                        if(application.ID.Trim().Length == 0) {
                            application.ID = Utils.GenID(true);
                        }
                        cmd.CommandText = string.Format("SELECT COUNT(displayname) FROM {0}applications WHERE displayname = @displayname", db.TablePrefix);
                        cmd.AddParameterWithValue("@displayname", application.DisplayName);
                        if(int.Parse((cmd.ExecuteScalar() ?? "0").ToString()) > 0) {
                            throw new ResourceBlenderException("Name already exists");
                        }
                        //todo: add DB constraints for unique keys - display name, application name
                        cmd.CommandText = string.Format("INSERT INTO {0}applications (id, name, displayname, defaultlanguage, languages) VALUES (@id, @name, @displayname, @defaultlanguage, @languages)", db.TablePrefix);
                        cmd.AddParameterWithValue("@id", application.ID);
                        cmd.AddParameterWithValue("@name", application.ApplicationName);
                        cmd.AddParameterWithValue("@displayname", application.DisplayName);
                        cmd.AddParameterWithValue("@defaultlanguage", application.DefaultLanguage);
                        cmd.AddParameterWithValue("@languages", Utils.Implode(application.Languages, ",", false));
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException(457, "Error saving application - " + ex.Message);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }
        
        /// <summary>
        /// Edits an existing application.
        /// </summary>
        /// <param name="application">The application.</param>
        public static void EditApplication(RBApplication application) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = string.Format("UPDATE {0}applications SET name = @name, displayname = @displayname, defaultlanguage = @defaultlanguage, languages = @languages WHERE id = @id", db.TablePrefix);
                        cmd.AddParameterWithValue("@id", application.ID);
                        cmd.AddParameterWithValue("@name", application.ApplicationName);
                        cmd.AddParameterWithValue("@displayname", application.DisplayName);
                        cmd.AddParameterWithValue("@defaultlanguage", application.DefaultLanguage);
                        cmd.AddParameterWithValue("@languages", Utils.Implode(application.Languages, ",", false));
                        cmd.ExecuteNonQuery();
                        /*System.Diagnostics.Debug.WriteLine("name: " + application.DisplayName);
                        System.Diagnostics.Debug.WriteLine("defaultlanguage: " + application.DefaultLanguage);
                        System.Diagnostics.Debug.WriteLine("languages: ");
                        foreach(string lang in application.Languages) {
                            System.Diagnostics.Debug.Write(lang);
                        }
                        System.Diagnostics.Debug.WriteLine("\n");*/
                    }
                    trans.Commit();
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException(457, "Error saving application - " + ex.Message);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }
        
        /// <summary>
        /// Deletes an application and it's associated bundles and elements.
        /// </summary>
        /// <param name="application">The application to delete.</param>
        public static void DeleteApplication(RBApplication application) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("DELETE FROM {0}applications WHERE id = @p", db.TablePrefix);
                    cmd.AddParameterWithValue("@p", application.ID);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = string.Format("DELETE FROM {0}bundles WHERE application = @p", db.TablePrefix);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = string.Format("DELETE FROM {0}elements WHERE applicationid = @p", db.TablePrefix);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Gets an application by ID.
        /// </summary>
        /// <param name="applicationIds">The application ID.</param>
        /// <param name="includeBundles"><c>true</c> if a bundle data should also be included.</param>
        /// <returns>
        /// The Application with the specified ID.
        /// </returns>
        public static RBApplication GetApplication(string applicationId, bool includeBundles) {
            IEnumerable<RBApplication> apps = GetApplications(new string[] { applicationId }, includeBundles);
            if(apps.Any()) {
                return apps.First();
            }
            throw new ArgumentException(string.Format(Resources.Errors.AppNotFoundName, applicationId));
        }

        /// <summary>
        /// Gets a list of all applications stored by ResourceBlender.
        /// </summary>
        /// <param name="includeBundles"><c>true</c> if bundle data should also be included.</param>
        /// <returns>
        /// A list of Applications containing all application details.
        /// </returns>
        public static List<RBApplication> GetApplications(bool includeBundles) {
            return GetApplications(null, includeBundles);
        }

        /// <summary>
        /// Gets a list of applications with the specified IDs.
        /// </summary>
        /// <param name="applicationIds">The application IDs.</param>
        /// <param name="includeBundles"><c>true</c> if bundle data should also be included.</param>
        /// <returns>
        /// A list of Applications with the specified ID containing all application details.
        /// </returns>
        public static List<RBApplication> GetApplications(string[] applicationIds, bool includeBundles) {
            List<RBApplication> applications = new List<RBApplication>();
            Database db = null;
            try {
                db = new Database();
                db.CheckConfig();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT * FROM {0}applications {1} ORDER BY displayname", db.TablePrefix, applicationIds == null ? string.Empty : "WHERE "+ db.GenerateInClause("id", applicationIds, false));
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        applications.Add(new RBApplication(reader));
                    }
                    reader.Close();
                    if(includeBundles) {
                        cmd.CommandText = string.Format("SELECT id, name FROM {0}bundles WHERE application = @app ORDER BY name", db.TablePrefix);
                        cmd.AddParameterWithValue("@app", "");
                        foreach(RBApplication app in applications) {
                            cmd.Parameters[0].Value = app.ID;
                            reader = cmd.ExecuteReader();
                            while(reader.Read()) {
                                app.Bundles.Add(reader["id"].ToString(), reader["name"].ToString());
                            }
                            reader.Close();
                        }
                    }
                }
            } catch(Exception ex) {
                throw ex;
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return applications;
        }
        #endregion

        #region Bundles
        /// <summary>
        /// Adds a resource bundle.
        /// </summary>
        /// <param name="bundle">The bundle.</param>
        /// <returns>The ID assigned to the bundle</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public static string AddBundle(RBBundle bundle) {
            if(string.IsNullOrEmpty(bundle.Name)) {
                throw new ResourceBlenderException(Resources.Errors.NameRequired);
            } else if(bundle.Name.Length > 255) {
                throw new ResourceBlenderException(string.Format(Resources.Errors.NameTooLongFmt, 255));
            }
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(name) FROM {0}bundles WHERE name = @name AND application = @appId", db.TablePrefix);
                    cmd.AddParameterWithValue("@name", bundle.Name);
                    cmd.AddParameterWithValue("@appId", bundle.ApplicationID);
                    int numExisting = Convert.ToInt32(cmd.ExecuteScalar());
                    if(numExisting > 0) {
                        throw new ResourceBlenderException(Resources.Errors.BundleAlreadyExists);
                    }

                    cmd.CommandText = string.Format("INSERT INTO {0}bundles (id, name, application) VALUES (@id, @name, @appId)", db.TablePrefix);
                    if(string.IsNullOrEmpty(bundle.BundleID)) {
                        bundle.BundleID = Utils.GenID(true);
                    }
                    cmd.AddParameterWithValue("@id", bundle.BundleID);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return bundle.BundleID;
        }

        /// <summary>
        /// Edits a resource bundle.
        /// </summary>
        /// <param name="bundle">The bundle.</param>
        /// <returns>The number of bundles affected.</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static int EditBundle(RBBundle bundle) {
            if(string.IsNullOrEmpty(bundle.Name)) {
                throw new ResourceBlenderException(Resources.Errors.NameRequired);
            } else if(bundle.Name.Length > 255) {
                throw new ResourceBlenderException(string.Format(Resources.Errors.NameTooLongFmt, 255));
            }
            Database db = null;
            int rows = 0;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(name) FROM {0}bundles WHERE name = @name AND application = @appId AND id != @bundleId", db.TablePrefix);
                    cmd.AddParameterWithValue("@name", bundle.Name);
                    cmd.AddParameterWithValue("@appId", bundle.ApplicationID);
                    cmd.AddParameterWithValue("@bundleId", bundle.BundleID);
                    int numExisting = Convert.ToInt32(cmd.ExecuteScalar());
                    if(numExisting > 1) {
                        throw new ResourceBlenderException(Resources.Errors.BundleAlreadyExists);
                    }
                    
                    cmd.CommandText = string.Format("UPDATE {0}bundles SET name = @name WHERE id = @bundleId", db.TablePrefix);
                    rows = cmd.ExecuteNonQuery();
                }
            } catch(Exception ex) {
                string error = ex.Message.ToLowerInvariant();
                if(error.Contains("constraint") || error.Contains("duplicate")) {
                    throw new ResourceBlenderException(Resources.Errors.BundleAlreadyExists);
                } else {
                    throw;
                }
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return rows;
        }

        /// <summary>
        /// Deletes a resource bundle and all elements it contains.
        /// </summary>
        /// <param name="bundle">The bundle.</param>
        /// <returns>The number of bundles deleted.</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public static int DeleteBundle(RBBundle bundle) {
            Database db = null;
            int rows = 0;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("DELETE FROM {0}bundles WHERE id = @bundleId", db.TablePrefix);
                    cmd.AddParameterWithValue("@bundleId", bundle.BundleID);
                    rows = cmd.ExecuteNonQuery();

                    cmd.CommandText = string.Format("DELETE FROM {0}elements WHERE bundle = @bundleId", db.TablePrefix);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return rows;
        }

        /// <summary>
        /// Gets all resource bundles for an application.
        /// </summary>
        /// <param name="applicationId">The application ID.</param>
        /// <returns>A list of bundles in the specified application.</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static IList<RBBundle> GetBundles(string applicationId) {
            Database db = null;
            List<RBBundle> bundles = new List<RBBundle>();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT * FROM {0}bundles WHERE application = @app", db.TablePrefix);
                    cmd.AddParameterWithValue("@app", applicationId);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        bundles.Add(new RBBundle(reader));
                    }
                }
            } catch {
                throw;
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return bundles;
        }

        /// <summary>
        /// Gets the number of elements used by one or more bundles.
        /// </summary>
        /// <param name="bundleIds">The bundle IDs.</param>
        /// <returns>The number of elements used by these bundles.</returns>
        public static int GetBundleElementCount(string[] bundleIds) {
            Database db = null;
            int numElements = 0;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(id) FROM {0}elements WHERE" + db.GenerateInClause("bundle", bundleIds, false), db.TablePrefix);
                    numElements = int.Parse(cmd.ExecuteScalar().ToString());
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException(452, "Error #452: Could not connect to database - " + ex.Message);
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return numElements;
        }
        #endregion

        #region Resources
        /// <summary>
        /// Gets a resource in all avaliable languages.
        /// </summary>
        /// <param name="resourceId">The resource ID.</param>
        /// <returns>The requested resource.</returns>
        public static RBResource GetResource(int resourceId) {
            var availableLanguages = GetUniqueLanguages();
            return GetResource(resourceId, availableLanguages.Select(lang => lang.Key).ToArray());
        }

        /// <summary>
        /// Gets a resource in a specified set of languages.
        /// </summary>
        /// <param name="resourceId">The resource ID.</param>
        /// <param name="languages">The languages to find for this resource.</param>
        /// <returns>The requested resource.</returns>
        public static RBResource GetResource(int resourceId, IEnumerable<string> languages) {
            Database db = null;
            RBResource resource = new RBResource(resourceId, string.Empty);
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    string crossTabColumns = db.GetCrosstabLanguageSql(languages);
                    cmd.CommandText = string.Format("SELECT {0} FROM {1}resources WHERE resourceid = @tID", crossTabColumns, db.TablePrefix);
                    cmd.AddParameterWithValue("@tID", resourceId);
                    DbDataReader reader = cmd.ExecuteReader();
                    if(reader.Read()) {
                        for(int i = 0; i < reader.FieldCount; i++) {
                            string languageCode = reader.GetName(i);
                            resource.Translations.Add(languageCode, new RBTranslation(languageCode, (reader[i] ?? string.Empty).ToString(), ""));
                        }
                    }
                    reader.Close();

                    // fill the translators' comments by changing the field name in the query
                    cmd.CommandText = cmd.CommandText.Replace("translation", "translatorcomment");
                    reader = cmd.ExecuteReader();
                    if(reader.Read()) {
                        for(int i = 0; i < reader.FieldCount; i++) {
                            resource.Translations[reader.GetName(i)].Comment = (reader[i] ?? string.Empty).ToString();
                        }
                    }
                    reader.Close();

                    // fill the overall resource comment
                    cmd.CommandText = "SELECT comment FROM " + db.TablePrefix + "resourcecomments WHERE resourceid = @tID";
                    resource.Comment = (cmd.ExecuteScalar() ?? string.Empty).ToString();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return resource;
        }

        /// <summary>
        /// Gets all elements and resources for an application.
        /// </summary>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="bundleIds">The bundle IDs.</param>
        /// <param name="languageCodes">The codes of the languages to include.</param>
        /// <returns>A DataTable containing (for each element) <c>elementID, applicationID, bundleID, elementName, resourceID, elementComment, resourceComment, bundleName, thent one column for every language specified</c></returns>
        //public static DataTable GetResources(string applicationId, string[] bundleIds, string[] languageCodes) {
        public static DataTable GetResources(string applicationId, IEnumerable<string> bundleIds, IEnumerable<string> languageCodes) {
            Database db = null;
            DataTable dtResults = new DataTable();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    StringBuilder sqlBuffer = new StringBuilder();
                    // this isn't the nicest code but it's a big beasty datatable so here we go...
                    string crossTabColumns, // the field selection part of the cross-tab query. Saves writing the same huge switch on both forms.
                           crossTabSql, // the whole crosstab query
                           bundleFormat = null,
                           crossTabEnd = null;
                    switch(db.InstanceType) {
                        case DatabaseType.MySQL:
                        case DatabaseType.SQLServer:
                            bundleFormat = "'{0}', ";
                            break;
                        case DatabaseType.SQLite:
                            bundleFormat = "\"{0}\", ";
                            break;
                    }
                    foreach(string bundle in bundleIds) {
                        sqlBuffer.AppendFormat(bundleFormat, bundle);
                    }
                    sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
                    string bundleSql = sqlBuffer.ToString();
                    sqlBuffer.Remove(0, sqlBuffer.Length);

                    switch(db.InstanceType) {
                        case DatabaseType.MySQL:
                            crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid 
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid 
                            WHERE applicationid = @pID AND bundle IN ({1}) 
                            GROUP BY e.id 
                            ORDER BY bundle, elementname ", db.TablePrefix, bundleSql);
                            break;
                        case DatabaseType.SQLServer:
                            crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                            WHERE applicationid = @pID  AND bundle IN ({1}) 
                            GROUP BY e.id,e.resourceid,applicationid,bundle,elementname,e.comment,c.comment,b.name
                            ORDER BY bundle, elementname", db.TablePrefix, bundleSql);
                            break;
                        case DatabaseType.SQLite:
                            crossTabEnd = string.Format(@" FROM {0}elements AS e
                            INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid
                            LEFT JOIN {0}bundles AS b ON b.id = e.bundle
                            LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                            WHERE applicationid = @pID  AND bundle IN ({1}) 
                            GROUP BY e.id,e.resourceid,applicationid,bundle,elementname,e.comment,c.comment,b.name
                            ORDER BY bundle, elementname", db.TablePrefix, bundleSql);
                            break;
                    }
                    crossTabColumns = db.GetCrosstabLanguageSql(languageCodes);
                    sqlBuffer.Append(crossTabColumns);

                    sqlBuffer.Insert(0, @"SELECT e.id, applicationid, bundle, elementname, e.resourceid, e.comment AS elementcomment, c.comment as resourcecomment, b.name AS bundlename,");
                    sqlBuffer.AppendFormat(crossTabEnd);
                    crossTabSql = sqlBuffer.ToString();
                    cmd.CommandText = crossTabSql;
                    cmd.AddParameterWithValue("@pID", applicationId);
                    DataAdapter adapter = new DataAdapter(cmd);
                    adapter.Fill(dtResults);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return dtResults;
        }

        /// <summary>
        /// Gets all translations for a resource. Each language is an entry in the dictionary.
        /// </summary>
        /// <param name="resourceid">The resource ID.</param>
        /// <returns>A key/value collection of languages/resources</returns>
        public static IDictionary<string,string> GetTranslationsByResourceID(int resourceId) {
            SortedList<string, string> resources = new SortedList<string, string>();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT language, translation FROM {0}resources WHERE resourceid = @tID", db.TablePrefix);
                    cmd.AddParameterWithValue("@tID", resourceId);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        resources.Add(reader[0].ToString(), reader[1].ToString());
                    }
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return resources;
        }

        /// <summary>
        /// Gets the ID and beginning of every resource in a language. This is useful for reducing network traffic depending on the size of the database. Eg: If a string
        /// resource is 2000 characters long and only the first 50 characters are shown to the user. If <paramref name="trimUsingSql"/> is not <c>true</c>, extra text in 
        /// translations is truncated.
        /// </summary>
        /// <param name="language">The language code.</param>
        /// <param name="numCharacters">The number of characters from the geginning of the resourse to show.</param>
        /// <returns>
        /// A collection of <c>{ResourceID, "Translation"}</c>, sorted by resource ID
        /// </returns>
        public static SortedDictionary<int, string> GetResourcesTrimmed(string language, int numCharacters) {
            return GetResourcesTrimmed(language, numCharacters, null);
        }

        /// <summary>
        /// Gets the ID and beginning of every resource in a language. This is useful for reducing network traffic depending on the size of the database. Eg: If a string
        /// resource is 2000 characters long and only the first 50 characters are shown to the user. If <paramref name="trimUsingSql"/> is not <c>true</c>, extra text in 
        /// translations is truncated.
        /// </summary>
        /// <param name="language">The language code.</param>
        /// <param name="numCharacters">The number of characters from the geginning of the resourse to show.</param>
        /// <param name="trimUsingSql">
        /// Whether translations should be trimmed using SQL.
        /// If <c>true</c>: each item contains "resourceId - translation..." as the value. Eg: Key=90, Value=90 - "Translat..."
        /// If <c>false</c> or <c>null</c>: each item contains "translation." as the value. Eg: Key=90, Value=90 - "Translat"
        /// </param>
        /// <returns>
        /// A collection of <c>{ResourceID, "Translation"}</c>, sorted by resource ID
        /// </returns>
        public static SortedDictionary<int, string> GetResourcesTrimmed(string language, int numCharacters, bool? trimUsingSql) {
            SortedDictionary<int, string> cache = new SortedDictionary<int, string>();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    string resourceSql = null; // the 'xxx - snippet...' selection query for generating the snippets
                    
                    if(trimUsingSql.HasValue && trimUsingSql.Value == true) {
                        int trimmedLength = numCharacters - 3 > 0 ? numCharacters - 3 : 1;
                        switch(db.InstanceType) {
                            case DatabaseType.MySQL:
                                resourceSql = string.Format(@"SELECT DISTINCT resourceid, 
                                            CAST(IF(CHAR_LENGTH(translation) < {0}, 
                                                CONCAT(resourceid, ' - ',translation), 
                                                CONCAT(resourceid, ' - ',SUBSTRING(translation,1,{1}),'...')
                                                ) AS CHAR) 
                                            AS display FROM {2}resources WHERE language = @defaultLang ORDER BY resourceid ASC", numCharacters, trimmedLength, db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                resourceSql = string.Format(@"SELECT DISTINCT resourceid, display = CAST(resourceid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < {0} THEN translation
                                             ELSE SUBSTRING(translation,1,{1})+'...'
                                            END
                                            FROM {2}resources WHERE language = @defaultLang ORDER BY resourceid ASC", numCharacters, trimmedLength, db.TablePrefix);
                                break;
                        }
                    } else {
                        switch(db.InstanceType) {
                            case DatabaseType.MySQL:
                                resourceSql = string.Format(@"SELECT DISTINCT resourceid, SUBSTR(translation,1,{0}) AS display FROM {1}resources WHERE language = @defaultLang ORDER BY resourceid ASC", numCharacters, db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                resourceSql = string.Format(@"SELECT DISTINCT resourceid, SUBSTRING(translation,1,{0}) AS display FROM {1}resources WHERE language = @defaultLang ORDER BY resourceid ASC", numCharacters, db.TablePrefix);
                                break;
                            case DatabaseType.SQLite:
                                resourceSql = string.Format(@"SELECT DISTINCT resourceid, SUBSTR(translation,1,{0}) AS display FROM {1}resources WHERE language = @defaultLang ORDER BY resourceid ASC", numCharacters, db.TablePrefix);
                                break;
                        }
                    }

                    cmd.AddParameterWithValue("@defaultLang", language);
                    cmd.CommandText = resourceSql;
                    DbDataReader reader = cmd.ExecuteReader();
                    cache.Add(-1, Resources.UIStrings.NewResource);
                    while(reader.Read()) {
                        cache.Add(int.Parse(reader["resourceid"].ToString()), reader["display"].ToString());
                    }
                    reader.Close();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return cache;
        }

        /// <summary>
        /// Gets translator comments in the specified languages for a resource.
        /// </summary>
        /// <param name="resourceId">The resource ID.</param>
        /// <param name="languages">The languages for which to find translator comments.</param>
        /// <returns>A collection of translator comments for the specified ID in the given languages. Key = languageCode, Value = Comment</returns>
        public static Dictionary<string, string> GetTranslatorComments(int resourceId, IEnumerable<string> languages) {
            Database db = null;
            Dictionary<string, string> comments = new Dictionary<string, string>();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT language, translatorcomment FROM {0}resources WHERE resourceid = {1} AND language IN ('{2}')", db.TablePrefix, resourceId, languages.Aggregate((items, item) => items + "','" + item));
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        comments[reader["language"].ToString()] = (reader["translatorcomment"] ?? string.Empty).ToString();
                    }
                    reader.Close();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return comments;
        }

        /// <summary>
        /// Gets the number of elements which use every resource for one language.
        /// </summary>
        /// <param name="languageCode">The language code.</param>
        /// <returns>A datatable of containing <c>resourceID, translation, number of resources with identical translations</c> for the specified language.</returns>
        public static DataTable GetDuplicateResourceCount(string languageCode) {
            DataTable dt = new DataTable();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    switch(db.InstanceType) {
                        case DatabaseType.MySQL:
                        case DatabaseType.SQLite:
                            cmd.CommandText = string.Format(@"SELECT r.resourceid, r.translation, COUNT(*) AS number FROM {0}resources AS r WHERE language = @lang GROUP BY translation HAVING COUNT(*) > 1 ORDER BY translation", db.TablePrefix);
                            break;
                        case DatabaseType.SQLServer:
                            cmd.CommandText = string.Format(@"SELECT translation,resourceid,number FROM
                                                (SELECT ROW_NUMBER() OVER (PARTITION BY translation ORDER BY resourceid DESC) 
                                                AS Seq,translation,resourceid,
                                                COUNT(resourceid) OVER (PARTITION BY translation) AS number
                                                FROM {0}resources
                                                WHERE language = @lang
                                                )t WHERE Seq=1 AND number > 1", db.TablePrefix);
                            break;
                    }
                    cmd.AddParameterWithValue("@lang", languageCode);

                    DataAdapter adapter = new DataAdapter(cmd);
                    adapter.Fill(dt);

                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return dt;
        }


        /// <summary>
        /// Gets details for a  duplicate resources.
        /// </summary>
        /// <param name="languages">The extra languages for which to show duplicates, or <c>null</c> for all languages.</param>
        /// <param name="resourceLanguage">The language for which to fnd duplicates.</param>
        /// <param name="resourceTranslation">The resource translation.</param>
        /// <returns>A datatable with columns for <c>resourceId, resourceComment, number of elements using that resource, then columns for every language specified</c>.</returns>
        /// <example>
        /// 	<c>GetDuplicateResources({"de", "fr", "en"}, "en", "About")</c> is called. All resources where the English translation is 'About' would be returned.
        ///     Two resources may have the same English translation but different French or German translations.
        /// </example>
        public static DataTable GetDuplicateResources(string[] languages, string resourceLanguage, string resourceTranslation) {
            Database db = null;
            DataTable dtResults = new DataTable();
            try {
                if(db == null || !db.Connected) {
                    db = new Database();
                    db.OpenConnection();
                }
                using(Command cmd = db.CreateCommand()) {
                    if(languages == null) { // show all languages
                        cmd.CommandText = string.Format("SELECT DISTINCT language FROM {0}resources", db.TablePrefix);
                        DbDataReader reader = cmd.ExecuteReader();
                        List<string> langs = new List<string>();
                        while(reader.Read()) {
                            string code = (reader[0] ?? string.Empty).ToString();
                            if(code.Length > 0) {
                                langs.Add(code);
                            }
                        }
                        reader.Close();
                        languages = langs.ToArray();
                    }

                    string crossTabColumns = db.GetCrosstabLanguageSql(languages),
                           crossTabSql = null;
                    switch(db.InstanceType) {
                        case DatabaseType.MySQL:
                            crossTabSql = string.Format(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, CAST(COUNT(e.resourceid) / (SELECT COUNT(resourceid) FROM {0}resources WHERE resourceid = tid)  AS SIGNED) AS numelements, {1}
                                           FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                                           LEFT JOIN {0}elements AS e ON r.resourceid = e.resourceid
                                           WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM {0}resources WHERE language = @lang AND translation = @translation)
                                           GROUP BY r.resourceid", db.TablePrefix, crossTabColumns);
                            break;
                        case DatabaseType.SQLServer:
                        case DatabaseType.SQLite:
                            crossTabSql = string.Format(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, COUNT(e.resourceid) / COALESCE(NULLIF((SELECT COUNT(r.resourceid) FROM {0}resources AS r WHERE r.resourceid = e.resourceid), 0), 1) AS numelements, {1}
                                          FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                                          LEFT JOIN {0}elements AS e ON r.resourceid = e.resourceid
                                          WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM {0}resources WHERE language = @lang AND translation = @translation)
                                          GROUP BY r.resourceid,
                                          c.comment,
                                          e.resourceid", db.TablePrefix, crossTabColumns);
                            break;
                    }
                    cmd.CommandText = crossTabSql;
                    cmd.AddParameterWithValue("@lang", resourceLanguage);
                    cmd.AddParameterWithValue("@translation", resourceTranslation);
                    DataAdapter adapter = new DataAdapter(cmd);
                    adapter.Fill(dtResults);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return dtResults;
        }

        /// <summary>
        /// For a specified language, gets all resources which are not used by any elements, and the number of other languages each resource has been translated into.
        /// </summary>
        /// <param name="language">The language for which to find unused resources.</param>
        /// <returns>A DataTable containing <c>resourceId, "translation", number of extra languages</c></returns>
        public static DataTable GetUnusedResources(string language) {
            Database db = null;
            DataTable dtAvailability = new DataTable();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format(@"SELECT t.resourceid, t.translation, (SELECT COUNT(r.language) FROM {0}resources AS r WHERE r.resourceid = t.resourceid) AS number
                                   FROM {0}resources AS t LEFT JOIN {0}elements AS e ON t.resourceid = e.resourceid WHERE language = @lang
                                   GROUP BY t.resourceid, t.translation HAVING COUNT(e.resourceid) = 0", db.TablePrefix);
                    cmd.AddParameterWithValue("@lang", language);
                    DataAdapter adapter = new DataAdapter(cmd);
                    adapter.Fill(dtAvailability);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return dtAvailability;
        }

        /// <summary>
        /// Searches for resources.
        /// </summary>
        /// <param name="languageCode">The language code of the of the search language.</param>
        /// <param name="searchText">The text for which to search (the standard SQL wildcard, <c>%</c> can be used).</param>
        /// <param name="applicationId">
        /// The ID of the application for which resources should be returned. If no ID is specified, all matching resources are returned, otherwise
        /// only resources which are used by the application are shown.
        /// </param>
        /// <returns>The search results.</returns>
        public static DataTable FindResources(string languageCode, string searchText, string applicationId) {
            DataTable dtResults = new DataTable();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    StringBuilder sqlBuffer;
                    if(applicationId != null && applicationId.Length > 0) {
                        sqlBuffer = new StringBuilder("SELECT r.resourceid, r.translation, c.comment, (SELECT COUNT(language) FROM {0}resources WHERE resourceid = r.resourceid) AS numlanguages ")
                                              .Append("FROM {0}elements AS e INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid ")
                                              .Append("WHERE language = @language AND translation LIKE @translation AND applicationid = @application ORDER BY translation");
                        cmd.AddParameterWithValue("@application", applicationId);
                    } else {
                        sqlBuffer = new StringBuilder("SELECT r.resourceid, r.translation, c.comment, (SELECT COUNT(language) FROM {0}resources WHERE resourceid = r.resourceid) AS numlanguages ")
                                              .Append("FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid ")
                                              .Append("WHERE language = @language AND translation LIKE @translation ORDER BY translation");
                    }
                    cmd.CommandText = string.Format(sqlBuffer.ToString(), db.TablePrefix);
                    cmd.AddParameterWithValue("@language", languageCode);
                    cmd.AddParameterWithValue("@translation", searchText);
                    DataAdapter adapter = new DataAdapter(cmd);
                    adapter.Fill(dtResults);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return dtResults;
        }

        /// <summary>
        /// Searches for resources using SQL paging to reduce unneeded data.
        /// </summary>
        /// <param name="languageCode">The language code of the of the search language.</param>
        /// <param name="searchText">The text for which to search (the standard SQL wildcard, <c>%</c> can be used).</param>
        /// <param name="offset">The offset the index of the first record to show.</param>
        /// <param name="limit">The maximum number of records to return.</param>
        /// <returns>
        /// A hashtable containing entries:
        /// resources: A list of search results,
        /// found: The total number of results available,
        /// perpage: The number per page,
        /// offset: The offset of the first record,
        /// language: The language code of the of the search language
        /// </returns>
        public static Hashtable FindResourcesPaged(string languageCode, string searchText, int offset, int limit) {
            List<ResourceSearchResult> resources = new List<ResourceSearchResult>();
            Database db = null;
            int found = 0;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    switch(db.InstanceType) {
                        case DatabaseType.MySQL:
                            cmd.CommandText = string.Format("SELECT r.resourceid, translation, comment FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid "
                                                         + " WHERE language = @language AND translation LIKE @translation ORDER BY translation LIMIT {1} OFFSET {2}", db.TablePrefix, limit, offset);
                            break;
                        case DatabaseType.SQLServer:
                            int pageNum = (offset / limit) + 1;
                            cmd.CommandText = string.Format(@";WITH results AS (
                                    SELECT r.resourceid, translation, comment, rowNo = ROW_NUMBER() OVER( ORDER BY translation ASC) 
                                    FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid 
                                    WHERE language = @language AND translation LIKE @translation
                                ) SELECT * FROM results
                                WHERE rowNo BETWEEN ({0}-1) * {1}+1 AND {0} * {1}", db.TablePrefix, pageNum, limit);
                            break;
                    }
                    // use HttpUtility.UrlDecode!
                    cmd.AddParameterWithValue("@language", languageCode);
                    cmd.AddParameterWithValue("@translation", '%' + searchText + '%');
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        // use HttpUtility.Urlencode for each value!
                        resources.Add(new ResourceSearchResult(reader[0].ToString(), reader[1].ToString(), reader[2].ToString()));
                    }
                    reader.Close();
                    cmd.CommandText = "SELECT COUNT(resourceid) FROM " + db.TablePrefix + "resources WHERE language = @language AND translation LIKE @translation";
                    found = int.Parse(cmd.ExecuteScalar().ToString());
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            Hashtable results = new Hashtable();
            results.Add("resources", resources);
            results.Add("found", found);
            results.Add("perpage", limit);
            results.Add("offset", offset);
            results.Add("language", languageCode); // could get this from the ID of the text box but it's easier/safer to pass it back to JS
            return results;
        }

        /// <summary>
        /// Saves a resource. Specify an <c>ID</c> of -1 to add a new resource.
        /// </summary>
        /// <param name="resource">The resource.</param>
        /// <returns>The ID which was saved.</returns>
        public static int SaveResource(RBResource resource) {
            List<string> errors = new List<string>();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmdUpdate = db.CreateCommand(trans)) {
                        if(resource.ID == -1) { // is there an existing resource ID, or is this a new resource
                            cmdUpdate.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                            int maxId = 0;
                            using(DbDataReader maxIdReader = cmdUpdate.ExecuteReader(CommandBehavior.SingleRow)) {
                                if(maxIdReader.HasRows) {
                                    maxIdReader.Read();
                                    int.TryParse(maxIdReader[0].ToString(), out maxId);
                                    maxId++;
                                }
                                maxIdReader.Close();
                            }
                            resource.ID = maxId;
                        }

                        // first add/update the translation and translator comment for each language
                        cmdUpdate.Parameters.Clear();
                        switch(db.InstanceType) {
                            case DatabaseType.MySQL:
                                cmdUpdate.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment", db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                cmdUpdate.CommandText = string.Format("UPDATE {0}resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)", db.TablePrefix);
                                break;
                            case DatabaseType.SQLite:
                                cmdUpdate.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)", db.TablePrefix);
                                break;
                        }
                        cmdUpdate.AddParameterWithValue("@tID", resource.ID);
                        cmdUpdate.AddParameterWithValue("@lang", "");
                        cmdUpdate.AddParameterWithValue("@trans", "");
                        cmdUpdate.AddParameterWithValue("@comment", "");
                        using(Command cmdDelete = db.CreateCommand(trans)) {
                            cmdDelete.CommandText = string.Format("DELETE FROM {0}resources WHERE resourceid = @tID AND language = @lang", db.TablePrefix);
                            cmdDelete.AddParameterWithValue("@tID", resource.ID);
                            cmdDelete.AddParameterWithValue("@lang", "");
                            foreach(KeyValuePair<string, RBTranslation> translation in resource.Translations) { // <code, translation>
                                if(string.IsNullOrEmpty(translation.Value.Translation)) { // delete the translation
                                    cmdDelete.Parameters["@lang"].Value = translation.Key;
                                    try {
                                        cmdDelete.ExecuteNonQuery();
                                    } catch(Exception ex) {
                                        errors.Add(string.Format("Error deleting {0} translation for resource #{1}: {2}", translation.Key, resource.ID, ex.Message));
                                    }
                                } else { // update/add the translation
                                    cmdUpdate.Parameters["@trans"].Value = translation.Value.Translation;
                                    cmdUpdate.Parameters["@lang"].Value = translation.Key;
                                    if(string.IsNullOrEmpty(translation.Value.Comment)) {
                                        cmdUpdate.Parameters["@comment"].Value = DBNull.Value;
                                    } else {
                                        cmdUpdate.Parameters["@comment"].Value = translation.Value.Comment;
                                    }
                                    try {
                                        cmdUpdate.ExecuteNonQuery();
                                    } catch(Exception ex) {
                                        errors.Add(string.Format("Error updating translation for {0}: {1}", translation.Key, ex.Message));
                                    }
                                }
                            }
                        }

                        // update the resource comment
                        if(!string.IsNullOrEmpty(resource.Comment)) {
                            cmdUpdate.Parameters.Clear();
                            switch(db.InstanceType) {
                                case DatabaseType.MySQL:
                                    cmdUpdate.CommandText = string.Format("INSERT INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment", db.TablePrefix);
                                    break;
                                case DatabaseType.SQLServer:
                                    cmdUpdate.CommandText = string.Format("UPDATE {0}resourcecomments SET comment = @comment WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment)", db.TablePrefix);
                                    break;
                                case DatabaseType.SQLite:
                                    cmdUpdate.CommandText = string.Format("INSERT OR REPLACE INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment)", db.TablePrefix);
                                    break;
                            }
                            cmdUpdate.AddParameterWithValue("@tID", resource.ID);
                            cmdUpdate.AddParameterWithValue("@comment", resource.Comment);
                            cmdUpdate.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return resource.ID;
        }

        /// <summary>
        /// Deletes a resource by ID.
        /// </summary>
        /// <param name="resourcesToDelete">The IDs of the resource to delete.</param>
        public static void DeleteResource(int resourceId) {
            DeleteResources(new int[] { resourceId });
        }

        /// <summary>
        /// Deletes resources by ID.
        /// </summary>
        /// <param name="resourcesToDelete">The IDs of the resources to delete.</param>
        public static void DeleteResources(IEnumerable<int> resourcesToDelete) {
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand())
                using(Command cmdDeleteComment = db.CreateCommand()) {
                    cmd.CommandText = string.Format("DELETE FROM {0}resources WHERE resourceid = @rID", db.TablePrefix);
                    cmdDeleteComment.CommandText = string.Format("DELETE FROM {0}resourcecomments WHERE resourceid = @rID", db.TablePrefix);
                    cmd.AddParameterWithValue("@rID", string.Empty);
                    cmdDeleteComment.AddParameterWithValue("@rID", string.Empty);
                    foreach(int id in resourcesToDelete) {
                        cmd.Parameters["@rID"].Value = id;
                        cmdDeleteComment.Parameters["@rID"].Value = id;
                        cmd.ExecuteNonQuery();
                        cmdDeleteComment.ExecuteNonQuery();
                    }
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Merges two or more resources.
        /// </summary>
        /// <param name="resourceIds">The IDs of the resources to merge.</param>
        /// <param name="preferredResource">The ID of the resource which should be kept in-case of conflicts.</param>
        public static void MergeResources(List<int> resourceIds, int preferredResource) {
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                StringBuilder errors = new StringBuilder();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmd = db.CreateCommand()) {
                        cmd.Transaction = trans;
                        Dictionary<string, RBTranslation> mergedResources = new Dictionary<string, RBTranslation>();
                        cmd.CommandText = string.Format("SELECT * FROM {0}resources WHERE{1}", db.TablePrefix, db.GenerateInClause("resourceid", resourceIds.ConvertAll<string>(i => i.ToString()), false));
                        DbDataReader reader = cmd.ExecuteReader();
                        string language;
                        while(reader.Read()) {
                            language = reader["language"].ToString();
                            if(mergedResources.ContainsKey(language)) { // another language has added this resource
                                int resourceId = Convert.ToInt32(reader["resourceid"]);
                                if(resourceId == preferredResource) {
                                    mergedResources[language] = new RBTranslation(language, reader["translation"].ToString(), reader["translatorcomment"].ToString());
                                }
                            } else {
                                mergedResources.Add(language, new RBTranslation(language, reader["translation"].ToString(), reader["translatorcomment"].ToString()));
                            }
                        }
                        reader.Close();

                        switch(db.InstanceType) {
                            case DatabaseType.MySQL:
                                cmd.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans,@translatorcomment) ON DUPLICATE KEY UPDATE translation = @trans", db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                cmd.CommandText = string.Format("UPDATE {0}resources SET translation = @trans, translatorcomment = @translatorcomment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @translatorcomment)", db.TablePrefix);
                                break;
                            case DatabaseType.SQLite:
                                cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans,@translatorcomment)", db.TablePrefix);
                                break;
                        }
                        cmd.AddParameterWithValue("@tID", resourceIds[0]); // merge all into the first ID which was selected, re-assign the rest to this ID later
                        cmd.AddParameterWithValue("@lang", "");
                        cmd.AddParameterWithValue("@trans", "");
                        cmd.AddParameterWithValue("@translatorcomment", "");
                        foreach(KeyValuePair<string, RBTranslation> translation in mergedResources) {
                            cmd.Parameters["@lang"].Value = translation.Value.LanguageCode;
                            cmd.Parameters["@trans"].Value = translation.Value.Translation;
                            if(translation.Value.Comment == null) {
                                cmd.Parameters["@translatorcomment"].Value = DBNull.Value;
                            } else {
                                cmd.Parameters["@translatorcomment"].Value = translation.Value.Comment;
                            }
                            try {
                                cmd.ExecuteNonQuery();
                            } catch(Exception ex) {
                                errors.AppendFormat("Failed to merge translation for {0} - '{1}': {2}\n", translation.Key, translation.Value, ex.Message);
                            }
                        }
                        if(errors.Length > 0) {
                            errors.Append("Errors encountered, skipping re-assigning elements\n");
                        } else {
                            cmd.CommandText = "UPDATE " + db.TablePrefix + "elements SET resourceid = @tID WHERE resourceid = @oldTId";
                            cmd.Parameters["@tID"].Value = preferredResource;
                            cmd.AddParameterWithValue("@oldTId", "");
                            for(int i = 1; i < resourceIds.Count; i++) {
                                cmd.Parameters["@oldTId"].Value = resourceIds[i];
                                try {
                                    cmd.ExecuteNonQuery();
                                } catch(Exception ex) {
                                    errors.AppendFormat("Failed to re-assign elements from translation #{0} to #{1}: {2}\n", resourceIds[0], resourceIds[i], ex.Message);
                                }
                            }
                            if(errors.Length > 0) {
                                errors.Append("Errors encountered, skipping re-assigning elements\n");
                            }
                        }
                    }
                    trans.Commit();
                }
                if(errors.Length > 0) {
                    throw new ResourceBlenderException("Completed with errors:\n" + errors);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Reassigns all elements using one resource to a different resource.
        /// </summary>
        /// <param name="oldId">The old resource ID.</param>
        /// <param name="resourceID">The new resource ID.</param>
        /// <returns>The number of elements re-assigned</returns>
        public static int ReassignElements(string oldId, int resourceID) {
            Database db = null;
            int numRows = 0;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("UPDATE {0}elements SET resourceid = @tID WHERE resourceid = @oldTId", db.TablePrefix);
                    cmd.AddParameterWithValue("@tID", resourceID);
                    cmd.AddParameterWithValue("@oldTId", oldId);
                    numRows = cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return numRows;
        }

        /// <summary>
        /// Duplication all translations from one language to another.
        /// </summary>
        /// <param name="sourceLanguage">The source language.</param>
        /// <param name="destinationLanguage">The destination language.</param>
        /// <param name="overwriteExisting"><c>true</c> resources which exist in the target language should be overwritten, otherwise <c>false</c>.</param>
        /// <param name="context">The context (used to check for cancel messages), or <c>null</c>.</param>
        /// <param name="logHandler">The delegate to use to log status messages, or <c>null</c>.</param>
        public static void CreateVariant(string sourceLanguage, string destinationLanguage, bool overwriteExisting, BackgroundWorker context, StatusMessage.StatusChangedEventHandler logHandler) {
            string sourceEnglishName,
                   destinationEnglishName;
            try {
                sourceEnglishName = CultureInfo.GetCultureInfo(sourceLanguage).EnglishName;
            } catch {
                sourceEnglishName = sourceLanguage;
            }
            try {
                destinationEnglishName = CultureInfo.GetCultureInfo(destinationLanguage).EnglishName;
            } catch {
                destinationEnglishName = destinationLanguage;
            }
            if(logHandler == null) {
                logHandler = new StatusMessage.StatusChangedEventHandler(message => Console.WriteLine(message));
            }
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmd = db.CreateCommand(trans)) {
                        cmd.Transaction = trans;
                        // read all resources from the source language
                        cmd.CommandText = string.Format("SELECT resourceid, translation FROM {0}resources WHERE language = @lang", db.TablePrefix);
                        cmd.AddParameterWithValue("@lang", sourceLanguage);
                        Dictionary<object, object> resources = new Dictionary<object, object>();
                        DbDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            resources.Add(reader[0], reader[1]);
                        }
                        reader.Close();

                        // copy all resources into the destination language
                        cmd.Parameters["@lang"].Value = destinationLanguage;
                        cmd.AddParameterWithValue("@tID", "");
                        cmd.AddParameterWithValue("@trans", "");
                        cmd.AddParameterWithValue("@comment", "");
                        int numCopied = 0;
                        if(overwriteExisting) {
                            switch(db.InstanceType) {
                                case DatabaseType.MySQL:
                                    cmd.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment", db.TablePrefix);
                                    break;
                                case DatabaseType.SQLServer:
                                    cmd.CommandText = string.Format("UPDATE {0}resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)", db.TablePrefix);
                                    break;
                                case DatabaseType.SQLite:
                                    cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)", db.TablePrefix);
                                    break;
                            }
                            foreach(KeyValuePair<object, object> resource in resources) { // <resource ID, translation>
                                cmd.Parameters["@tID"].Value = resource.Key;
                                cmd.Parameters["@trans"].Value = resource.Value;
                                try {
                                    cmd.ExecuteNonQuery();
                                    logHandler(new StatusMessage("Overwriting translation for resource #" + resource.Key + "...", StatusMessage.StatusMessageType.Success));
                                    numCopied++;
                                } catch(Exception ex) { // any errors are SQL errors
                                    logHandler(new StatusMessage("Error adding translation for resource #" + resource.Key + ": " + ex.Message, StatusMessage.StatusMessageType.Error));
                                }
                                if(context != null && context.CancellationPending) {
                                    throw new Exception("Aborted");
                                }
                            }
                        } else {
                            cmd.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)", db.TablePrefix);
                            foreach(KeyValuePair<object, object> resource in resources) { // <resource ID, translation>
                                cmd.Parameters["@tID"].Value = resource.Key;
                                cmd.Parameters["@trans"].Value = resource.Value;
                                try {
                                    cmd.ExecuteNonQuery();
                                    logHandler(new StatusMessage("Copied translation for resource #" + resource.Key + "...", StatusMessage.StatusMessageType.Success));
                                    numCopied++;
                                } catch { // errors thrown when a translation exists
                                    logHandler(new StatusMessage("Translation for resource #" + resource.Key + " exists, skipping...", StatusMessage.StatusMessageType.Error));
                                }
                            }
                            if(context != null && context.CancellationPending) {
                                throw new Exception("Aborted");
                            }
                        }
                        logHandler(new StatusMessage(string.Format("Copied {0} translations from {1} to {2}", numCopied, sourceEnglishName, destinationEnglishName), StatusMessage.StatusMessageType.Status));
                    }
                    trans.Commit();
                }
            } catch(Exception ex) {
                logHandler(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }
        #endregion

        #region Resource Statistics
        /// <summary>
        /// Gets the total number of translations available for a collection of resources.
        /// </summary>
        /// <param name="resourceIds">The resource IDs.</param>
        /// <returns>
        /// The total number of translations available for the specified resources.
        /// </returns>
        public static int GetResourceAvailability(int[] resourceIds) {
            Database db = null;
            int numLanguages = -1;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(language) FROM {0}resources WHERE {1}", db.TablePrefix, db.GenerateInClause("resourceid", resourceIds.Select(id => id.ToString()).ToArray(), false));
                    numLanguages = Convert.ToInt32(cmd.ExecuteScalar() ?? 0);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return numLanguages;
        }

        /// <summary>
        /// Gets the number of elements used by a collection of resources.
        /// </summary>
        /// <param name="resourceIds">The resource IDs.</param>
        /// <returns>A count of the number of elements used by the specified resources.</returns>
        public static int GetAffectedElements(int[] resourceIds) {
            Database db = null;
            int elements = -1;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(id) FROM {0}elements WHERE {1}", db.TablePrefix, db.GenerateInClause("resourceid", resourceIds.Select(id => id.ToString()).ToArray(), false));
                    elements = Convert.ToInt32(cmd.ExecuteScalar() ?? 0);
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return elements;
        }
        
        /// <summary>
        /// Gets a completion summary for the specified applications.
        /// </summary>
        /// <param name="applicationIds">The IDs of the applications for which to show a completion summary.</param>
        /// <param name="includeNotInUse"><c>true</c> if unused languages should be shown, otherwise <c>false</c>.</param>
        /// <returns>
        /// A list of completion summaries for the specified applications.
        /// </returns>
        public static List<ApplicationSummary> GetResourceCompletionSummary(IEnumerable<string> applicationIds, bool includeNotInUse) {
            Database db = null;
            List<string> errors = new List<string>();
            List<ApplicationSummary> resourceSummaries = new List<ApplicationSummary>();
            try {
                db = new Database();
                db.OpenConnection();
                string cmdApplicationInfo = string.Format("SELECT displayname, defaultLanguage, languages FROM {0}applications WHERE id = @app", db.TablePrefix),
                       cmdBundles = string.Format("SELECT name, (SELECT COUNT(bundle) FROM {0}elements AS e WHERE e.bundle = b.id) AS number FROM {0}bundles AS b WHERE application = @app", db.TablePrefix),
                       cmdCompletion = string.Format("SELECT COUNT(language) FROM {0}elements AS e LEFT JOIN {0}resources AS r ON e.resourceid = r.resourceid WHERE language = @abbrev AND applicationid = @app", db.TablePrefix),
                       cmdFindExtra = string.Format("SELECT DISTINCT language FROM {0}elements AS e LEFT JOIN {0}resources AS r ON e.resourceid = r.resourceid WHERE applicationid = @app", db.TablePrefix);
                using(Command cmd = db.CreateCommand()) {
                    cmd.AddParameterWithValue("@app", "");
                    cmd.AddParameterWithValue("@abbrev", "");
                    foreach(string appId in applicationIds) {
                        cmd.CommandText = cmdApplicationInfo;
                        cmd.Parameters["@app"].Value = appId;
                        try {
                            DbDataReader reader = cmd.ExecuteReader();
                            reader.Read();
                            List<string> languagesInApp = new List<string>(reader["languages"].ToString().Split(','));
                            string displayName = reader["displayname"].ToString(),
                                   defaultLang = reader["defaultlanguage"].ToString();
                            reader.Close();
                            ApplicationSummary summary = new ApplicationSummary(displayName);

                            if(includeNotInUse) { // check for resources not found by the application's 'Languages' list
                                cmd.CommandText = cmdFindExtra;
                                reader = cmd.ExecuteReader();
                                while(reader.Read()) {
                                    string extraCode = (reader[0] ?? string.Empty).ToString();
                                    if(!languagesInApp.Contains(extraCode)) {
                                        languagesInApp.Add(extraCode);
                                    }
                                }
                                reader.Close();
                            }

                            cmd.CommandText = cmdBundles;
                            reader = cmd.ExecuteReader();
                            string bundleFormat = "{0} ({1} {2})";
                            while(reader.Read()) {
                                try {
                                    int numElements = reader.GetInt32(1);
                                    summary.Bundles.Add(string.Format(bundleFormat, reader["name"].ToString(), numElements, numElements == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements));
                                } catch(Exception ex) {
                                    errors.Add(Resources.Errors.CouldNotReadBundle + ": " + ex.Message);
                                }
                            }
                            reader.Close();

                            cmd.CommandText = cmdCompletion;
                            cmd.Parameters["@abbrev"].Value = defaultLang;
                            try {
                                summary.TotalElements = int.Parse(cmd.ExecuteScalar().ToString());
                            } catch {
                                throw new Exception(Resources.Errors.ErrorReadingNumElements);
                            }

                            // the map is sorted by the english name of the languages it contains, but the abbreviation is needed to find the flag image
                            foreach(string abbrev in languagesInApp) {
                                cmd.Parameters["@abbrev"].Value = abbrev;
                                int numTranslations = 0;
                                string flag = null;
                                CultureInfo culture = null;
                                try {
                                    numTranslations = int.Parse(cmd.ExecuteScalar().ToString());
                                    culture = CultureInfo.GetCultureInfo(abbrev);
                                    flag = Utils.GetISO3166(culture);
                                } catch(Exception ex) {
                                    culture = Utils.GetCultureInfo(abbrev);
                                    Console.WriteLine("Error: Could not parse culture for " + abbrev + ": " + ex.Message);
                                }
                                summary.CompletionMap.Add(new LanguageCompletion(culture, flag, numTranslations)); // famfamfam's flags comply with ISO3166 thankfully
                            }
                            summary.CompletionMap.Sort(new Comparison<LanguageCompletion>(delegate(LanguageCompletion a, LanguageCompletion b) {
                                return b.Culture.EnglishName.CompareTo(a.Culture.EnglishName);
                            }));
                            resourceSummaries.Add(summary);
                        } catch(Exception ex) {
                            errors.Add(ex.Message);
                        }
                    }
                }
                /* //todo: summary of all applications/average completion?
                 * if(plcDynamic.Controls.Count > 1) {
                    StringBuilder sqlBuffer = new StringBuilder("SELECT COUNT(language) FROM ").Append(db.TablePrefix).Append("elements AS e LEFT JOIN ").Append(db.TablePrefix).Append("resources AS r ON e.resourceid = r.resourceid WHERE language = @abbrev"+db.GenerateInClause("applicationid", ,true));
                    cmd.CommandText = sqlBuffer.ToString();
                    cmd.Parameters["@abbrev"].Value = defaultLang;
                }*/
            } catch(Exception ex) {
                errors.Add("Error connecting to the database: " + ex.Message);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            if(errors.Count > 0) {
                errors.Insert(0, "Non-fatal errors:");
                throw new MultipleException(errors);
            }
            return resourceSummaries;
        }

        /// <summary>
        /// Gets details about which applications, bundles and elements are used by a resource.
        /// </summary>
        /// <param name="resourceId">The resource ID.</param>
        /// <returns>A Dictionary containing <c>"ApplicationName", {"BundleName - ElementName"}</c></returns>
        public static Dictionary<string, List<string>> GetResourceUsage(int resourceId) {
            Database db = null;
            Dictionary<string, List<string>> applications = new Dictionary<string, List<string>>(); // application, bundle - element
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format(@"SELECT a.displayname AS app, b.name AS bundle, e.elementname AS element FROM {0}elements AS e 
                                                          INNER JOIN {0}bundles as b ON bundle = b.id INNER JOIN {0}applications AS a ON a.id = b.application 
                                                          WHERE resourceid = @resourceId ORDER BY displayname, bundle, elementname", db.TablePrefix);
                    cmd.AddParameterWithValue("@resourceId", resourceId);
                    DbDataReader reader = cmd.ExecuteReader();
                    string app,
                           bundle,
                           element,
                           lastApp = string.Empty,
                           lastBundle = string.Empty,
                           lastElement = string.Empty;
                    while(reader.Read()) {
                        app = reader["app"].ToString();
                        bundle = reader["bundle"].ToString();
                        element = reader["element"].ToString();
                        if(app != lastApp) {
                            lastApp = app;
                            applications.Add(app, new List<string>());
                        }
                        applications[app].Add(string.Format("{0} - {1}", bundle, element));
                    }

                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return applications;
        }
        #endregion

        #region Elements
        /// <summary>
        /// Add multiple elements in a language to a single application/bundle.
        /// </summary>
        /// <param name="applicationId">The ID of the application to add the translations.</param>
        /// <param name="bundle">The ID of the resource bundle.</param>
        /// <param name="resourceLanguage">The language code of the translations.</param>
        /// <param name="newTranslations">The new translations.</param>
        /// <param name="logHandler">The delegate to use to log status messages, or <c>null</c>.</param>
        public static void BulkAddElements(string applicationId, string bundle, string resourceLanguage, IEnumerable<string> newTranslations, StatusMessage.StatusChangedEventHandler logHandler) {
            Database db = null;
            //todo: this could possibly be simplified and merged with SaveResource()
            try {
                if(newTranslations.Count() == 0) {
                    throw new ResourceBlenderException("No elements found");
                }
                if(logHandler == null) {
                    logHandler = new StatusMessage.StatusChangedEventHandler(message => Console.WriteLine(message));
                }
                db = new Database();
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmdCheckExisting = db.CreateCommand(trans))
                    using(Command cmdFindSimilar = db.CreateCommand(trans))
                    using(Command cmdInsertElement = db.CreateCommand(trans))
                    using(Command cmdInsertResource = db.CreateCommand(trans))
                    using(Command cmdMaxRow = db.CreateCommand(trans)) {
                        cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                        cmdCheckExisting.AddParameterWithValue("@appId", applicationId);
                        cmdCheckExisting.AddParameterWithValue("@bundle", bundle);
                        cmdCheckExisting.AddParameterWithValue("@element", "");

                        cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                        cmdFindSimilar.AddParameterWithValue("@language", resourceLanguage);
                        cmdFindSimilar.AddParameterWithValue("@translation", "");

                        cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid) VALUES (@appId, @bundle, @element, @tId)";
                        cmdInsertElement.AddParameterWithValue("@appId", applicationId);
                        cmdInsertElement.AddParameterWithValue("@bundle", bundle);
                        cmdInsertElement.AddParameterWithValue("@element", "");
                        cmdInsertElement.AddParameterWithValue("@tId", "");

                        switch(db.InstanceType) {
                            case DatabaseType.MySQL:
                                cmdInsertResource.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation) VALUES (@tId, @language, @translation) ON DUPLICATE KEY UPDATE translation = @translation", db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                cmdInsertResource.CommandText = string.Format("UPDATE {0}resources SET translation = @translation WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation) VALUES (@tId, @language, @translation)", db.TablePrefix);
                                break;
                            case DatabaseType.SQLite:
                                cmdInsertResource.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation) VALUES (@tId, @language, @translation)", db.TablePrefix);
                                break;
                        }
                        cmdInsertResource.AddParameterWithValue("@tID", "");
                        cmdInsertResource.AddParameterWithValue("@language", resourceLanguage);
                        cmdInsertResource.AddParameterWithValue("@translation", "");

                        cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                        int numElementsAdded = 0,
                            numResourcesAdded = 0,
                            elementCounter = 1;
                        Regex rgxElementName = new Regex("[^0-9A-Za-z*]");
                        foreach(string newTranslation in newTranslations) {
                            try {
                                if(newTranslation.Length == 0) {
                                    continue;
                                }
                                // remove all spaces, then remove all non ASCII chars from the translation to get the element name
                                //string elementName = Encoding.ASCII.GetString(Encoding.ASCII.GetBytes((newTranslation.Replace(" ", "")))).Replace("?", "").Replace(",", "");
                                string elementName = rgxElementName.Replace(newTranslation, "");
                                if(elementName.Length > 30) {
                                    elementName = elementName.Remove(29);
                                } else if(elementName.Length == 0) { // the translation might have contained just intl. chars
                                    elementName = string.Format("Element{0}", elementCounter++);
                                }
                                string trimmedTranslation = Utils.TrimText(newTranslation, 30);
                                int resourceId = 0;

                                cmdFindSimilar.Parameters["@translation"].Value = newTranslation;
                                DbDataReader reader = cmdFindSimilar.ExecuteReader();
                                if(reader.HasRows) { // already a resource like this, just create a new element
                                    reader.Read();
                                    int.TryParse(reader[0].ToString(), out resourceId);
                                    reader.Close();
                                    logHandler(new StatusMessage(string.Format("An identical resource exists for '{0}', adding an element...", trimmedTranslation), StatusMessage.StatusMessageType.Status));
                                } else { // no existing resource, add one
                                    reader.Close();
                                    // find highest resource ID
                                    using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                        if(maxIdReader.HasRows) {
                                            maxIdReader.Read();
                                            int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                            resourceId++;
                                        }
                                        maxIdReader.Close();
                                    }
                                    cmdInsertResource.Parameters["@tId"].Value = resourceId;
                                    cmdInsertResource.Parameters["@translation"].Value = newTranslation;
                                    try {
                                        cmdInsertResource.ExecuteNonQuery();
                                        numResourcesAdded++;
                                    } catch(Exception ex) {
                                        logHandler(new StatusMessage(string.Format("Error adding resource '{0}': {1}", trimmedTranslation, ex.Message), StatusMessage.StatusMessageType.Error));
                                        continue;
                                    }
                                }
                                cmdCheckExisting.Parameters["@element"].Value = elementName;
                                reader = cmdCheckExisting.ExecuteReader();
                                if(reader.HasRows) { // element exists in this bundle, add some random characters to the end
                                    elementName = elementName + Utils.GenID(true);
                                }
                                reader.Close();

                                cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                cmdInsertElement.Parameters["@element"].Value = elementName;
                                cmdInsertElement.ExecuteNonQuery();
                                numElementsAdded++;
                                logHandler(new StatusMessage(string.Format("'{0}' added as element '{1}' (resource #{2})", trimmedTranslation, elementName, resourceId), StatusMessage.StatusMessageType.Success));
                            } catch(Exception ex) {
                                logHandler(new StatusMessage(string.Format("Error adding resource '{0}': {1}", newTranslation, ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                        logHandler(new StatusMessage(string.Format("Added {0} resources and {1} elements", numResourcesAdded, numElementsAdded), StatusMessage.StatusMessageType.Success));
                    }
                    trans.Commit();
                }
            } catch(Exception ex) {
                logHandler(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Saves an element. Specify an <c>ID</c> of -1 to add a new element.
        /// </summary>
        /// <param name="element">The element.</param>
        /// <param name="desiredResourceID">The desired resource ID.</param>
        /// <param name="bundleID">The ID of the bundle to which this element should be saved added.</param>
        public static void SaveElement(RBElement element, int desiredResourceID, string bundleID) {
            // first save the resource this element uses.
            RBResource selectedResource = element.Resource;
            selectedResource.ID = desiredResourceID;
            int newResourceId = SaveResource(selectedResource); // a new ID may have been assigned if the resource did not exist
            element.Resource.ID = newResourceId;

            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(DbTransaction trans = db.BeginTransaction()) {
                    using(Command cmdUpdate = db.CreateCommand(trans)) {
                        // update the element
                        
                        if(element.ID > 0) {
                            cmdUpdate.CommandText = string.Format("UPDATE {0}elements SET bundle = @bundle, elementname = @element, resourceid = @rID, comment = @comment WHERE id = @id", db.TablePrefix);
                            cmdUpdate.AddParameterWithValue("@id", element.ID);
                        } else {
                            cmdUpdate.CommandText = string.Format("INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@appId, @bundle, @element, @rID, @comment)", db.TablePrefix);
                            cmdUpdate.AddParameterWithValue("@appId", element.Application.ID);
                        }
                        cmdUpdate.AddParameterWithValue("@rID", newResourceId);
                        cmdUpdate.AddParameterWithValue("@bundle", bundleID);
                        cmdUpdate.AddParameterWithValue("@element", element.Name);
                        cmdUpdate.AddParameterWithValue("@comment", element.Comment);
                        cmdUpdate.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException("Error saving element: " + ex.Message, ex);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Deletes an element.
        /// </summary>
        /// <param name="p">The application ID.</param>
        /// <param name="elementId">The element ID.</param>
        public static void DeleteElement(string applicationId, string elementId) {
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = "DELETE FROM " + db.TablePrefix + "elements WHERE applicationid = @pID AND id = @eID";
                    cmd.AddParameterWithValue("@pID", applicationId);
                    cmd.AddParameterWithValue("@eID", elementId);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }
        #endregion

        #region Templates
        /// <summary>
        /// Adds a template.
        /// </summary>
        /// <param name="template">The template.</param>
        public static void AddTemplate(RBTemplate template) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(id) FROM {0}templates WHERE name = @name", db.TablePrefix);
                    cmd.AddParameterWithValue("@name", template.Name);
                    int numExistingTemplates = int.Parse(cmd.ExecuteScalar().ToString());
                    if(numExistingTemplates > 0) {
                        throw new Exception(Resources.Errors.TemplateAlreadyExists);
                    }
                    cmd.CommandText = string.Format("INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES (@name, @filename, @filestart, @line, @fileend)", db.TablePrefix);
                    cmd.AddParameterWithValue("@filename", template.FileName);
                    cmd.AddParameterWithValue("@filestart", template.FileStart);
                    cmd.AddParameterWithValue("@line", template.FileLine);
                    cmd.AddParameterWithValue("@fileend", template.FileEnd);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Edits a template.
        /// </summary>
        /// <param name="template">The template.</param>
        public static void EditTemplate(RBTemplate template) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(id) FROM {0}templates WHERE name = @name AND id != @id", db.TablePrefix);
                    cmd.AddParameterWithValue("@name", template.Name);
                    cmd.AddParameterWithValue("@id", template.ID);
                    int numExistingTemplates = int.Parse(cmd.ExecuteScalar().ToString());
                    if(numExistingTemplates > 0) {
                        throw new Exception("Template already exists");
                    }
                    cmd.CommandText = string.Format("UPDATE {0}templates SET name = @name, filename = @filename, filestart = @filestart, line = @line, fileend = @fileend WHERE id = @id", db.TablePrefix);
                    cmd.AddParameterWithValue("@filename", template.FileName);
                    cmd.AddParameterWithValue("@filestart", template.FileStart);
                    cmd.AddParameterWithValue("@line", template.FileLine);
                    cmd.AddParameterWithValue("@fileend", template.FileEnd);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Deletes a template.
        /// </summary>
        /// <param name="templateId">The template ID.</param>
        public static void DeleteTemplate(int templateId) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("DELETE FROM {0}templates WHERE id = @templateId", db.TablePrefix);
                    cmd.AddParameterWithValue("@templateId", templateId);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Gets the types of resources which can be generated, including all templates.
        /// </summary>
        /// <returns>A collection of <c>typeName, typeID</c></returns>
        public static SortedDictionary<string, string> GetResourceGenerationTypes() {
            SortedDictionary<string, string> resourceTypes = new SortedDictionary<string, string>();
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    foreach(KeyValuePair<string, string> template in EnumBinder.EnumHelper.ToList(typeof(Constants.ResourceType))) {
                        resourceTypes.Add(template.Value, template.Key);
                    }
                    cmd.CommandText = "SELECT id, name FROM " + db.TablePrefix + "templates";
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        resourceTypes.Add(reader["name"].ToString(), reader["id"].ToString());
                    }
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException(453, "Error #453: Could not load resource formats - " + ex.Message);
            } finally {
                try {
                    db.CloseConnection();
                } catch { }
            }
            return resourceTypes;
        }

        /// <summary>
        /// Gets a user created resource generation template.
        /// </summary>
        /// <param name="templateId">The template ID.</param>
        /// <returns>The template with the specifed ID.</returns>
        public static RBTemplate GetTemplate(string templateId) {
            Database db = null;
            RBTemplate template = new RBTemplate();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "templates WHERE id = @templateId";
                    cmd.AddParameterWithValue("@templateId", templateId);
                    DbDataReader reader = cmd.ExecuteReader();
                    if(!reader.Read()) {
                        throw new ArgumentException(string.Format(Resources.Errors.TemplateNotFoundName, templateId));
                    }
                    template.ID = Convert.ToInt32(reader["id"] ?? "-1");
                    template.Name = reader["name"].ToString();
                    template.FileName = reader["filename"].ToString();
                    template.FileStart = reader["filestart"].ToString();
                    template.FileEnd = reader["fileend"].ToString();
                    template.FileLine = reader["line"].ToString();
                }
            } catch(Exception ex) {
                throw new ResourceBlenderException(Resources.Errors.CouldNotLoadTemplateData + ": " + ex.Message);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return template;
        }

        /// <summary>
        /// Gets all user created templates.
        /// </summary>
        /// <param name="templateId">The template ID.</param>
        /// <returns>The template with the specifed ID.</returns>
        public static IEnumerable<RBTemplate> GetTemplates() {
            Database db = null;
            List<RBTemplate> templates = new List<RBTemplate>();
            try {
                db = new Database();
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT * FROM {0}templates", db.TablePrefix);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        try {
                            templates.Add(new RBTemplate(Convert.ToInt32(reader["id"] ?? "-1"),
                                reader["name"].ToString(),
                                reader["filename"].ToString(),
                                reader["filestart"].ToString(),
                                reader["fileend"].ToString(),
                                reader["line"].ToString()));
                        } catch(Exception ex) {
                            Console.WriteLine("Invalid template: "+ex.Message);
                        }
                    }
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return templates;
        }

        /// <summary>
        /// Saves a template. Specify an <c>ID</c> of -1 to add a new template.
        /// </summary>
        /// <param name="template">The template.</param>
        public static void SaveTemplate(RBTemplate template) {
            Database db = new Database();
            try {
                db.OpenConnection();
                using(Command cmd = db.CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT COUNT(id) FROM {0}templates WHERE name = @name AND id != @id", db.TablePrefix);
                    cmd.AddParameterWithValue("@name", template.Name);
                    cmd.AddParameterWithValue("@id", template.ID);
                    int numExistingTemplates = int.Parse(cmd.ExecuteScalar().ToString());
                    if(numExistingTemplates > 0) {
                        throw new Exception("Template already exists");
                    }
                    if(template.ID > 0) {
                        cmd.CommandText = string.Format("UPDATE {0}templates SET name = @name, filename = @filename, filestart = @filestart, line = @line, fileend = @fileend WHERE id = @id", db.TablePrefix);
                    } else {
                        cmd.CommandText = string.Format("INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES (@name, @filename, @filestart, @line, @fileend)", db.TablePrefix);
                    }
                    cmd.AddParameterWithValue("@filename", template.FileName);
                    cmd.AddParameterWithValue("@filestart", template.FileStart);
                    cmd.AddParameterWithValue("@line", template.FileLine);
                    cmd.AddParameterWithValue("@fileend", template.FileEnd);
                    cmd.ExecuteNonQuery();
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }
        #endregion
    }
}
